Query settings are set for a given query to govern the operation of the query in a specific Discover report. This allows users to tweak the way a query is resolved and its performance. Once set, the setting will drive the report and its subsequent usage in the platform.
Note: Query settings are only relevant for Microsoft Multidimensional, Tabular and SAP BW models and cubes. They are NOT relevant in Pyramid models.
Settings for Microsoft Multidimensional and Tabular
Microsoft's MDX engine has gone under numerous changes since its inception. Due to differences in versions, differences between Multidimensional and Tabular, as well as differences n cube / model design, there are some elements of the MDX query construction that need to be modulated. The following settings will allow advanced user's to tweak how the MDX query is executed.
- Optimize: Enables 'non-empty' logic on all filter, N-of-N and sort operations to improve processing times. This feature will not work in some cube scenarios and if the top level of a cube is empty.
- Measure Optimization: Enables 'non-empty' logic on measure selections to improve processing times. This feature will not work in some cube scenarios.
- Optimize Totals: Enables 'non-empty' logic on all total operations to improve processing times. This feature will not work in some cube scenarios and if the top level of a cube is empty.
- Context Heuristics: Applies 'EXISTING' functional logic to various operations to force the injection of context values into different parts of the query when using two or more attributes from the same dimension. It is used to correct for logical errors in Microsoft's MDX engine.
- Auto-include Calculated Members: This switch enables all functional queries to include server-defined custom members in results.
- Sub Query Mode: This instructs the query engine to use sub-queries in MDX when making multiple background ("where") selections. Sub-querying can often be more efficient than normal query structures. However, they have limitations and cannot be used universally.
- Use Explicit Lists: This option creates a list in the query's 'WITH' statement for any list or multi-select filter used in the Discovery.
- Remove Filter Scoping: Removes the Scope_Isolation function from the filter query.
- Cross Filtering: If a regular filter is set in one report and a context-based multi-select filter based on a dynamic list is also selected, the filter in the second filter will be based on the selection in the first filter.
Settings for SAP BW
- Optimize Totals: Enables 'non-empty' logic on all total operations to improve processing times. This feature will not work in some cube scenarios and if the top level of a cube is empty.
- Sub Query Mode: This instructs the query engine to use sub-queries in MDX when making multiple background ("where") selections. Sub-querying can often be more efficient than normal query structures. However, they have limitations and cannot be used universally.
Settings for SQL (Relational Databases)
- Optimize: Enables the SQL engine to join multiple queries together to make a query run more efficiently, If this option is not selected, the Pyrana engine will run smaller but more fragmented queries, This feature is enabled by default for all new discoveries.
- Sub Query Mode: This instructs the query engine to use sub-queries. This enables the Pyrana engine to make use of the ANSI SQL sub-query functionality. This can be used to optimize a query execution to run multiple logical steps in one SQL query.
User Defaults
Several key settings can be driven from the 'user defaults' capability in the admin console and/or in the user's personal default settings (or both).